package com.changan.alm2.version.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SqlServerToMysqlDDLConverter {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=test";
        String username = "sa";
        String password = "Aa123456";
        
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();

            ResultSet tables = statement.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                Statement innerStatement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet columns = innerStatement.executeQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "'");

                StringBuilder ddl = new StringBuilder("CREATE TABLE " + tableName + " (\n");
                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME");
                    String dataType = columns.getString("DATA_TYPE");
                    int columnSize = columns.getInt("CHARACTER_MAXIMUM_LENGTH");

                    ddl.append("  " + columnName + " " + convertDataType(dataType, columnSize) + ",\n");
                }
                columns.close();
                ddl.setLength(ddl.length() - 2); // 移除最后一个逗号和换行符
                ddl.append("\n);");

                System.out.println(ddl.toString());
                innerStatement.close();
            }
            tables.close();
            
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static String convertDataType(String dataType, int size) {
        // 这里根据需要将SQL Server的数据类型转换成对应的MySQL数据类型
        if (dataType.equalsIgnoreCase("int")) {
            return "INT";
        } else if (dataType.equalsIgnoreCase("varchar")) {
            return "VARCHAR(" + size + ")";
        } else if (dataType.equalsIgnoreCase("datetime")||dataType.equalsIgnoreCase("datetime2")) {
            return "DATETIME";
        }
        // ... 其他数据类型的转换
        
        return dataType;
    }
}
